package com.game.commons.database;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;






/**
 * DB
 * @author 杜祥
 * @create 2013-6-29
 */
public final class DB 
{
	private static Logger log = Logger.getLogger(DB.class);
	
	
	/**
	 * 执行select查询
	 * @param query				SQL语句
	 * @param reader			模板
	 * @return					返回是否成功
	 */
	public static boolean select(String query, ReadStH reader)
	{
		return select(query, reader, null);
	}
	
	/**
	 * 执行select查询。<br>
	 * 使用ReadStH或ReadStH子类,利用setParam()和handleRead()方法设置查询语句参数和查询语句返回结果集处理。
	 * @param query					SQL语句
	 * @param reader				模板
	 * @param errMsg				错误消息
	 * @return 						返回是否成功
	 */
	public static boolean select(String query, ReadStH reader, String errMsg)
	{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rset;

		try
		{
			con = DatabaseFactory.getConnection();
			stmt = con.prepareStatement(query);
			if (reader instanceof ParamReadStH)
				((ParamReadStH) reader).setParams(stmt);
			rset = stmt.executeQuery();
			reader.handleRead(rset);
			
		}
		catch (Exception e)
		{
			if (errMsg == null)
				log.warn("错误的执行select查询 " + e, e);
			else
				log.warn(errMsg + " " + e, e);
			return false;
		}
		finally
		{
			try
			{
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			}
			catch (Exception e)
			{
				log.warn("关闭数据库连接失败" + e, e);
			}
		}
		return true;
	}
	
	/**
	 *  调用存储过程
	 *  
	 * @param query
	 * @param reader
	 * @return
	 */
	public static boolean call(String query, ReadStH reader)
	{
		return call(query, reader, null);
	}
	
	/**
	 * 调用存储过程
	 * @param query				SQL语句
	 * @param readStH			模板
	 * @param errMsg			错误消息
	 * @return			true 成功
	 * 					false 失败
	 */
	public static boolean call(String query, ReadStH readStH, String errMsg)
	{
		Connection con = null;
		CallableStatement stmt = null;
		ResultSet rset;
		
		try 
		{
			con = DatabaseFactory.getConnection();
			stmt = con.prepareCall(query);
			if(readStH instanceof CallReadStH)
				((CallReadStH)readStH).setParams(stmt);
			rset = stmt.executeQuery();
			readStH.handleRead(rset);
		} 
		catch(Exception e)
		{
			if(errMsg == null)
				log.warn("调用存储过程错误！", e);
			else
				log.warn(errMsg, e);
			return false;
		}
		finally
		{
			try
			{
				if(con != null)
					con.close();
				if(stmt != null)
					stmt.close();
			}
			catch(Exception e)
			{
				log.warn("关闭数据库连接失败！ " + e, e);
			}
		}
		return true;
	}
	
	
	/**
	 * 执行插入和更新语句。
	 * @param query				SQL语句
	 * @param errMsg			错误消息
	 * @return 	true成功返回
	 * 			false失败返回
	 */
	public static boolean insertUpdate(String query, String errMsg)
	{
		return insertUpdate(query, null, errMsg);
	}

	/**
	 * 执行插入语句、更新语句、查询语句。
	 * 利用{@link IUStH}来对执行的插入、更新、查询语句进行编辑，必须手动去执行SQL语句。
	 * @param query							SQL语句
	 * @param batch							模板
	 * @return 	true成功返回
	 * 			false失败返回
	 */
	public static boolean insertUpdate(String query, IUStH batch)
	{
		return insertUpdate(query, batch, null);
	}
	
	/**
	 * 执行插入语句、更新语句语句。
	 * 利用{@link IUStH}来对执行的插入、更新、查询语句进行编辑，必须手动去执行SQL语句。
	 * @param query							SQL语句
	 * @param batch							模板
	 * @param errMsg						错误消息
	 * @return 	true成功返回
	 * 			false失败返回
	 */
	public static boolean insertUpdate(String query, IUStH batch, String errMsg)
	{
		Connection con = null;
		PreparedStatement stmt = null;

		try
		{
			con = DatabaseFactory.getConnection();
			stmt = con.prepareStatement(query);
			if (batch != null)
				batch.handleInsertUpdate(stmt);
			else
				stmt.executeUpdate();
		}
		catch (Exception e)
		{
			if (errMsg == null)
				log.warn("查询执行失败。 ", e);
			else
				log.warn(errMsg, e);

			return false;
		}
		finally
		{
			try
			{
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			}
			catch (Exception e)
			{
				log.warn("关闭数据库连接失败 ", e);
			}
		}
		return true;
	}
	
	
	/**
	 * 开始一个新的事物
	 * @return		新的事物对象
	 * @throws SQLException 
	 * 				无法创建事物时抛出
	 */
	public static Transaction beginTransation() throws SQLException
	{
		Connection con = DatabaseFactory.getConnection();
		
		return new Transaction(con);
	}
	
	
	/**
	 * 用指定的SQL创建PreparedStatement。<br>
	 * 默认的结果集类型 {@link java.sql.ResultSet#TYPE_FORWARD_ONLY} <br>
	 * 默认的并发类型{@link java.sql.ResultSet#CONCUR_READ_ONLY}
	 * @param sql
	 * @return
	 */
	public static PreparedStatement prepareStatement(String sql)
	{
		return prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	}
	
	/**
	 * 用指定的sql创建PreparedStatement
	 * @param sql
	 * 					SQL
	 * @param resultSetType
	 * 				结果集类型<br>
	 * 				<code>ResultSet.TYPE_FORWARD_ONLY</code>,<br>
	 *            	<code>ResultSet.TYPE_SCROLL_INSENSITIVE</code>, or <br>
	 *            	<code>ResultSet.TYPE_SCROLL_SENSITIVE</code>
	 * @param resultSetConcurrency
	 * 				并发类型<br>
	 *  			<code>ResultSet.CONCUR_READ_ONLY</code> or <br>
	 *            	<code>ResultSet.CONCUR_UPDATABLE</code>
	 * @return
	 */
	public static PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
	{
		Connection c = null;
		PreparedStatement ps = null;
		try
		{
			c = DatabaseFactory.getConnection();
			ps = c.prepareStatement(sql, resultSetType, resultSetConcurrency);
		}
		catch (Exception e)
		{
			log.error("无法用指定的SQL创建PreparedStatement: " + sql, e);
			if (c != null)
			{
				try
				{
					c.close();
				}
				catch (SQLException e1)
				{
					log.error("无法关闭连接", e1);
				}
			}
		}

		return ps;
	}
	
	
	/**
	 * 执行PreparedStatement
	 * @param statement
	 * @return		返回执行结果,参见{@link java.sql.PreparedStatement#executeUpdate()}，另外-1为错误结果返回。
	 */
	public static int executeUpdate(PreparedStatement statement)
	{
		try
		{
			return statement.executeUpdate();
		}
		catch(Exception e)
		{
			log.error("无法执行PreparedStatement的executeUpdate方法", e);
		}

		return -1;
	}
	
	
	/**
	 * 执行PreparedStatement,并关闭他的连接。
	 * @param statement		
	 */
	public static void executeUpdateAndClose(PreparedStatement statement)
	{
		executeUpdate(statement);
		close(statement);
	}
	
	
	/**
	 * 执行查询并返回ResultSet
	 * @param statement
	 * @return
	 */
	public static ResultSet executeQuerry(PreparedStatement statement)
	{
		ResultSet rs = null;
		try
		{
			rs = statement.executeQuery();
		}
		catch(Exception e)
		{
			log.error("在执行查询时出错", e);
		}
		return rs;
	}
	
	
	/**
	 * 关闭PreparedStatemet
	 * @param statement
	 */
	public static void close(PreparedStatement statement)
	{

		try
		{
			if(statement.isClosed())
			{
				log.warn("尝试关闭已经关闭的PreparedStatement", new Exception());
				return;
			}

			Connection c = statement.getConnection();
			statement.close();
			c.close();
		}
		catch(Exception e)
		{
			log.error("关闭PreparedStatement出错", e);
		}
	}
}
